from sqlalchemy import insert,select,update,delete,func,join,engine
from sqlalchemy.sql import text,and_,or_
from sqlalchemy.engine import Connection, CursorResult, LegacyRow,Transaction
from eim.DataBasModals.mode import t_userinfo,t_project
from eim.tools.dbtool import DBTool
from eim.tools.searchtool import SearchCondition,SearchConditionList,PartnerRS,MatchLogic
from eim.result.page_result import PageResult
from eim import db,tokenScheme,secret_key
from eim import tokenScheme

import jwt,time


class UserService:
    """
    操作员维护服务
    """

    # 构造函数
    def __init__(self,db):
        """
        用户操作类

        Args:
            db (_type_): 数据库连接
        """
        self.db = db
    
    # 创建安全token
    def createToken(self, usercode, name, uid,workno,rolecode, exprietime: float):
        """
        根据用户名及id获取token
        :param uno: 用户编码
        :param uname: 用户名
        :param uid: 用户id
        :param workno:员工工号
        :param rolecode: 操作员的角色编号
        :param exprietime: 过期时长分钟
        :return: token
        """
        try:
            # 定义token结构
            time_start = time.time()
            tokenstruct: dict = {
                "exp": time_start + (exprietime * 60),
                "iat": time_start,
                "iss": "com.jasonzhang1224.163",
                "data": {
                    "usercode": usercode,
                    "name": name,
                    "workno":workno,
                    "rolecode":rolecode,
                    "id": uid
                }
            }
            token = jwt.encode(tokenstruct, key=secret_key)
            return f"{tokenScheme} {token}"
        except Exception as es:
            raise(es)
    
    # 添加新的系统操作员
    def appendUser(self,usercode:str,workno:str,username:str,password:str,pcode:str,rolecode:str)->int:
        """
        增加新的员工

        Args:
            workno (str): 员工编码
            username (str): 员工姓名
            activestatus (str): 激活状态
            password (str): 登录密码
            loginstatus (str): 登录状态
            pcode (str): 隶属项目
            rolecode(str): 隶属角色

        Returns:
            int: 返回受影响行数
        """
        dbtool = DBTool(db)
        try:
            if(usercode=='')or(username==''):
                raise Exception('员工编号和员工姓名不能为空')
            pwd = password
            if(pwd!=''):
                pwd =dbtool.encryLoginPassword(pwd)
            else:
                pwd=tokenScheme
                pwd=dbtool.encryLoginPassword(pwd)
            vals = {'usercode':usercode,'workno':workno,'name':username,'activestatus':'00','password':pwd,'loginstatus':'00','pcode':pcode,'rolecode':rolecode}
            con = db.engine.connect()
            qry = insert(t_userinfo).values(vals)
            res:CursorResult=con.execute(qry)
            return res.inserted_primary_key[0]
        except Exception as es:
            raise es
        
    # 根据员工ID更新员工信息    
    def updateUser(self,id:str,params:dict)->int:
        """
        更新员工信息
        Args:
            id (str): 员工ID
            params(dict): 员工更新参数
        """
        try:
            pms = params
            if('password' in pms.keys()):
                dbtool = DBTool(db)
                pms["password"]=dbtool.encryLoginPassword(pms["password"])
                
            if( 'activestatus' in pms.keys() and pms["activestatus"] not in ('00','01')):
                raise Exception("用户激活状态编码不正确")
            if('loginstatus' in pms.keys() and pms["loginstatus"] not in ('00','01')):
                raise Exception('登录状态不正确')
            
            qry = update(t_userinfo).values(pms)
            qry = qry.where(text(f"id='{id}'"))
            con = db.engine.connect() 
            res:CursorResult=con.execute(qry)
            return res.rowcount
        except Exception as es:
            raise es

    # 删除操作员
    def deleteUser(self,id:int)->int:
        """
        根据指定的id删除操作员,操作员只能停用不能删除

        Args:
            id (str): 员工id

        Returns:
            int: 返回受影响行数
        """
        try:
            con:Connection = db.engine.connect()
            qry = update(t_userinfo).values(activestatus='01')
            qry = qry.where(text(f"id='{id}'"))
            res:CursorResult = con.execute(qry)
            return res.rowcount
        except Exception as es:
            raise(es)
    
    def batchDelUser(self,ids:tuple)->int:
        """
        批量停用操作员
        Args:
            ids (tuple): 待停用操作员的ID列表
        Returns:
            int: 返回受影响行数
        """
        try:
            con:Connection = db.engine.connect()
            qry= update(t_userinfo).values(activestatus='01')
            qry = qry.where(text(f"id in {ids}"))
            res:CursorResult = con.execute(qry)
            return res.rowcount
        except Exception as es:
            raise es
        
    def batchActionUser(self,ids:tuple)->int:
        """
        批量激活操作员
        Args:
            ids (tuple): 待停用操作员的ID列表
        Returns:
            int: 返回受影响行数
        """
        try:
            con:Connection = db.engine.connect()
            qry= update(t_userinfo).values(activestatus='00')
            qry = qry.where(text(f"id in {ids}"))
            res:CursorResult = con.execute(qry)
            return res.rowcount
        except Exception as es:
            raise es
    
    def batchResetPassword(self,ids:tuple,pwd:str)->int:
        """
        批量重置操作员密码
        Args:
            ids (tuple): 操作员ID列表
            pwd (str): 新密码
        Returns:
            int: 受影响的行数
        """
        try:
            dbtool = DBTool(db)
            encrypwd = dbtool.encryLoginPassword(pwd)
            con:Connection = db.engine.connect()
            qry = update(t_userinfo).values(password=encrypwd)
            qry = qry.where(text(f"ID in {ids}"))
            res:CursorResult = con.execute(qry)
            return res.rowcount
        except Exception as es:
            raise es
    
    # 通过id获取员工信息
    def getUserByID(self,id)->dict:
        """
        通过员工id获取员工信息
        Args:
            id (_type_): 员工ID
        Returns:
            dict: 指定的员工信息
        """
        try:
            con:Connection = db.engine.connect()
            qry = select(t_userinfo)
            qry = qry.where(text(f"id={id}"))
            res:LegacyRow = con.execute(qry).first()
            return res._asdict()
        except Exception as es:
            raise(es)
    
    # 员工登录
    def userLogin(self,workno:str,pwd:str,loginIP:str)->dict:
        """
        员工登录

        Args:
            workno (str): 员工工号
            pwd (str): 登录密码
            loginIP(str): 远程登录的IP
        Raises:
            Exception: 员工登录异常或数据库访问异常等

        Returns:
            dict: 员工信息
        """     
        con:Connection = db.engine.connect()
        tran:Transaction = con.begin()
        try:
            qry=select(t_userinfo)
            qry = qry.where(text(f"workno='{workno}' and activestatus='00'")).limit(1).offset(0)
            qry = qry.with_for_update()
            res:LegacyRow = con.execute(qry).first()
            if(res==None):
                raise Exception("操作员编号或密码错误")
            user = res._asdict()
            if(user["activestatus"]=='01'):
                raise Exception("操作员编码未激活!")
            if(user["loginstatus"]=='01' and user["remoteip"]!=loginIP and user["remoteip"]!='127.0.0.1'):
                raise Exception("操作员编号为锁定状态,当前不可使用")
            password = user["password"]
            from eim.tools.dbtool import DBTool
            dbtool = DBTool(db)
            curpwd = dbtool.encryLoginPassword(pwd)
            if(password!=curpwd):
                raise Exception("操作员编号或密码错误!")
            else:
                uid = user["ID"]
                q = update(t_userinfo).values(loginstatus='01',remoteip=loginIP)
                q = q.where(text(f"ID='{uid}'"))
                con.execute(q)
                tran.commit()
                del(user["password"])
                del(user["loginstatus"])
                return user
        except Exception as es:
            tran.rollback()
            raise(es)
        
    def checkpwd(self,userid:int,pwd:str)->bool:
        """
        校验密码是否正确
        Args:
            userid (int): 待校验ID的操作员id
            pwd (str): 待校验的密码

        Returns:
            bool: 校验的结果
        """
        try:
            con:Connection =db.engine.connect()
            from eim.tools.dbtool import DBTool
            dbtool = DBTool(db)
            qry = select(t_userinfo).where(t_userinfo.c.ID==userid).limit(1).offset(0)
            res:CursorResult = con.execute(qry)
            user = res.first()
            dbpwd = user._asdict()["password"]
            checkpwd = dbtool.encryLoginPassword(pwd)
            if(dbpwd==checkpwd):
                return True
            else:
                return False
        except Exception as es:
            raise(es)
        
    
    def pageUsers(self,pn:int,ps:int,fields:list,pmlst:SearchConditionList)->dict:
        """
        根据检索条件分页获取操作员信息

        Args:
            pn (int): 分页页码
            ps (int): 页面尺寸
            fields(list): 数据字段列表
            pmlst (SearchConditionList): 分页检索条件

        Returns:
            dict: 返回分页结果数据
        """
        try:
             
            pn=1 if pn<=0 else pn
            ps=30 if ps<=0  else ps
            offset =(pn-1)*ps
            
            if(not isinstance(pmlst,SearchConditionList)):
                raise Exception("请确保检索条件类型错误")
            if(not isinstance(fields,list)):
                raise Exception("字段列表格式不正确")

            
            cds=pmlst.getConditionStr()
            from eim.tools.dbtool import DBTool
            dt = DBTool(db)
            if cds!=None:
                qry_fd=select(dt.genDBcolumns(t_userinfo,fields)).where(text(cds))
                qry_cnt=select(func.count(t_userinfo.c.ID)).where(text(cds))
            else:
                qry_fd=select(dt.genDBcolumns(t_userinfo,fields))
                qry_cnt=select(func.count(t_userinfo.c.ID))
            
            qry_fd=qry_fd.limit(ps).offset(offset)
            
            # 获取满足条件的记录总数量
            con:Connection = db.engine.connect()
            res:CursorResult = con.execute(qry_cnt)
            cnt:int = res.scalar()
            
            # 获取满足条件的分页记录
            recList = []
            res:CursorResult = con.execute(qry_fd)
            row:LegacyRow=None
            for row in res.fetchall():
                recList.append(row._asdict())
            
            pr = PageResult(pn,ps,cnt,recList)
            return pr.toDict()
        except Exception as es:
            raise es

